﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
using System.Reflection;


namespace SQLServerDAL
{
  public class SQLHelper
  {
    protected static readonly log4net.ILog log =
   log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
    #region 通用方法
    // 数据连接池  
    private SqlConnection con;
    /// <summary>  
    /// 返回数据库连接字符串  
    /// </summary>  
    /// <returns></returns>  
    public static string GetSqlConnection()
    {
      //localTest  remoteDB
      return System.Configuration.ConfigurationManager.AppSettings["localTest"];
    }
    #endregion
    #region 执行sql字符串
    /// <summary>  
    /// 执行不带参数的SQL语句  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public static int ExecuteSql(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        try
        {
          SqlCommand cmd = new SqlCommand();
          cmd.Connection = conn;
          cmd.CommandText = Sqlstr;
          conn.Open();
          cmd.ExecuteNonQuery();
          conn.Close();
          return 1;
        }
        catch (Exception ex)
        {
          log.Error("执行语句【" + Sqlstr + "】的时候发生错误，错误信息是：" + ex.Message);
          return 0;
        }
      }
    }
    /// <summary>  
    /// 执行带参数的SQL语句  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <param name="param">参数对象数组</param>  
    /// <returns></returns>  
    public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        try
        {
          SqlCommand cmd = new SqlCommand();
          cmd.Connection = conn;
          cmd.CommandText = Sqlstr;
          cmd.Parameters.AddRange(param);
          conn.Open();
          cmd.ExecuteNonQuery();
          conn.Close();
          return 1;
        }
        catch (Exception ex)
        {
          log.Error("执行语句【" + Sqlstr + "】的时候发生错误，错误信息是：" + ex.Message);
          return 0;
        }
      }
    }
    /// <summary>  
    /// 返回DataReader  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public static SqlDataReader ExecuteReader(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
      try
      {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = Sqlstr;
        conn.Open();
        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
      }
      catch (Exception ex)
      {
        log.Error("执行语句【" + Sqlstr + "】的时候发生错误，错误信息是：" + ex.Message);
        return null;
      }
    }
    public static SqlDataReader ExecuteReader(String Sqlstr, SqlParameter[] paras)
    {
      String ConnStr = GetSqlConnection();
      SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
      try
      {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = Sqlstr;
        cmd.Parameters.AddRange(paras);
        conn.Open();
        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
      }
      catch (Exception ex)
      {
        log.Error("执行语句【" + Sqlstr + "】的时候发生错误，错误信息是：" + ex.Message);
        return null;
      }
    }
    /// <summary>  
    /// 执行SQL语句并返回数据表  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public static DataTable ExecuteDt(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      SqlDataAdapter da = null;
      DataTable dt = null;
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        try
        {
          da = new SqlDataAdapter(Sqlstr, conn);
          dt = new DataTable();
          conn.Open();
          da.Fill(dt);
          conn.Close();
        }
        catch (Exception ex)
        {
          log.Error("执行语句【" + Sqlstr + "】的时候，发生了异常，异常信息是：" + ex.Message);
        }
        return dt;
      }
    }
    /// <summary>  
    /// 执行带参数SQL语句并返回数据表  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <param name="param">参数对象数组</param>  
    /// <returns></returns>  
    public static DataTable ExecuteDt(String Sqlstr, SqlParameter[] param)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
        da.SelectCommand.Parameters.AddRange(param);
        DataTable dt = new DataTable();
        conn.Open();
        da.Fill(dt);
        conn.Close();
        return dt;
      }
    }
    /// <summary>  
    /// 执行SQL语句并返回DataSet  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public static DataSet ExecuteDs(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
        DataSet ds = new DataSet();
        conn.Open();
        da.Fill(ds);
        conn.Close();
        return ds;
      }
    }

    public static int ExecuteScalar(string Sqlstr, SqlParameter[] param)
    {
      object result = 0;
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = Sqlstr;
        cmd.Parameters.AddRange(param);
        result = cmd.ExecuteScalar();
        conn.Close();
      }

      return result == null ? 0 : 1;
    }

    public static int ExecuteScalar(string Sqlstr)
    {
      int result = 0;
      string ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = Sqlstr;
        result = (int)cmd.ExecuteScalar();
      }
      return result;
    }
    #endregion
    #region 操作存储过程
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <returns>存储过程的返回值</returns>  
    public int RunProc(string procName)
    {
      SqlCommand cmd = CreateCommand(procName, null);
      cmd.ExecuteNonQuery();
      this.Close();
      return (int)cmd.Parameters["ReturnValue"].Value;
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <returns>存储过程的返回值</returns>  
    public int RunProc(string procName, SqlParameter[] prams)
    {
      SqlCommand cmd = CreateCommand(procName, prams);
      cmd.ExecuteNonQuery();
      this.Close();
      return (int)cmd.Parameters[0].Value;
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="dataReader">结果集</param>  
    public void RunProc(string procName, out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(procName, null);
      dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <param name="dataReader">结果集</param>  
    public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(procName, prams);
      dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    public DataSet RunProc(string procName, SqlParameter[] prams, string tableName)
    {
      DataSet ds = new DataSet();
      SqlCommand cmd = CreateCommand(procName, prams);
      SqlDataAdapter da = new SqlDataAdapter();
      da.SelectCommand = cmd;
      da.Fill(ds, tableName);
      return ds;
      //dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

    }
    /// <summary>  
    /// 创建Command对象用于访问存储过程  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <returns>Command对象</returns>  
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
      // 确定连接是打开的  
      Open();
      //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );  
      SqlCommand cmd = new SqlCommand(procName, con);
      cmd.CommandType = CommandType.StoredProcedure;
      // 添加存储过程的输入参数列表  
      if (prams != null)
      {
        foreach (SqlParameter parameter in prams)
          cmd.Parameters.Add(parameter);
      }
      // 返回Command对象  
      return cmd;
    }
    /// <summary>  
    /// 创建输入参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <param name="Value">参数值</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
      return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }
    /// <summary>  
    /// 创建输出参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
      return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }
    /// <summary>  
    /// 创建存储过程参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <param name="Direction">参数的方向(输入/输出)</param>  
    /// <param name="Value">参数值</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
      SqlParameter param;
      if (Size > 0)
      {
        param = new SqlParameter(ParamName, DbType, Size);
      }
      else
      {
        param = new SqlParameter(ParamName, DbType);
      }
      param.Direction = Direction;
      if (!(Direction == ParameterDirection.Output && Value == null))
      {
        param.Value = Value;
      }
      return param;
    }
    #endregion
    #region 数据库连接和关闭
    /// <summary>  
    /// 打开连接池  
    /// </summary>  
    private void Open()
    {
      // 打开连接池  
      if (con == null)
      {
        //这里不仅需要using System.Configuration;还要在引用目录里添加  
        con = new SqlConnection(GetSqlConnection());
        con.Open();
      }
    }
    /// <summary>  
    /// 关闭连接池  
    /// </summary>  
    public void Close()
    {
      if (con != null)
        con.Close();
    }
    /// <summary>  
    /// 释放连接池  
    /// </summary>  
    public void Dispose()
    {
      // 确定连接已关闭  
      if (con != null)
      {
        con.Dispose();
        con = null;
      }
    }
    #endregion


  }
}


//}
